package cc.comac.mom.util;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;

import com.google.common.collect.Lists;

public class ExportUtil {



    private String timeout = "10000";

    @Value("${oss.endpoint}")
    private String endpoint;

    @Value("${ons.accessKey}")
    private String accessKeyId;

    @Value("${ons.secretKey}")
    private String accessKeySecret;

    @Value("${oss.bucketName}")
    private String bucketName;

    /**
     * @param sheetName
     * @param titleName
     * @throws Exception
     */
    public XSSFWorkbook execute(String sheetName, String titleName, List<String> columnNames, List<String> keys, List dataList) {
        return execute(sheetName, titleName, Lists.newArrayList(), columnNames, keys, dataList, true);
    }

    /**
     * @param sheetName
     * @param titleName
     * @throws Exception
     */
    public XSSFWorkbook execute(String sheetName, String titleName, List<String> columnNames, List<String> keys, List dataList, Boolean sortNeed) {
        return execute(sheetName, titleName, Lists.newArrayList(), columnNames, keys, dataList, sortNeed);
    }

    /**
     * @param sheetName
     * @param titleName
     * @throws Exception
     */
    public XSSFWorkbook execute(String sheetName, String titleName, List<Map<Integer, String>> mainInfoList, List<String> columnNames, List<String> keys, List dataList, Boolean sortNeed) {
        // 第一步，创建一个webbook，对应一个Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet();
        //sheet.setDefaultColumnWidth(20); //统一设置列宽
        int firstRowNum = 0;
        if (StringUtils.isNotEmpty(titleName)) {
            // 创建第0行 也就是标题
            XSSFRow row1 = sheet.createRow(firstRowNum);
            row1.setHeightInPoints(50);// 设备标题的高度
            // 第三步创建标题的单元格样式style2以及字体样式headerFont1
            XSSFCellStyle style2 = wb.createCellStyle();
            style2.setAlignment(HorizontalAlignment.CENTER);
            style2.setVerticalAlignment(VerticalAlignment.CENTER);

            style2.setFillForegroundColor((short) 0x29);
            //style2.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_TURQUOISE.getIndex());
            style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            XSSFFont headerFont1 = wb.createFont();
            headerFont1.setBold(Boolean.TRUE); // 字体加粗
            headerFont1.setFontName("黑体"); // 设置字体类型
            headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
            style2.setFont(headerFont1); // 为标题样式设置字体样式
            XSSFCell cell1 = row1.createCell(0);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
                    columnNames.size())); // 合并列标题
            cell1.setCellValue(titleName); // 设置值标题
            cell1.setCellStyle(style2); // 设置标题样式
            firstRowNum++;
        }
        //创建第二行数据
        int count = 0;
        for (Map<Integer, String> mainInfo : mainInfoList) {
            XSSFRow mainRow = sheet.createRow(firstRowNum);
            mainRow.setHeight((short) 400);
            int cellNum = 0;
            for (Map.Entry<Integer, String> cell : mainInfo.entrySet()) {
                sheet.addMergedRegion(new CellRangeAddress(firstRowNum, firstRowNum, cellNum * 2, (cellNum * 2) + 1));
                XSSFCell cell1 = mainRow.createCell(cellNum * 2);
                cell1.setCellValue(mainInfo.get(cell.getValue()));
                cellNum++;
            }
            firstRowNum++;
            count++;
        }
        // 创建第1行 也就是表头
        XSSFRow row = sheet.createRow(firstRowNum);
        row.setHeightInPoints(15);// 设置表头高度

        // 第四步，创建表头单元格样式 以及表头的字体样式
        XSSFCell cell;
        if (sortNeed) {
            cell = row.createCell(0);
            cell.setCellValue("序号");
        }
        for (int i = 1; i <= columnNames.size(); i++) {
            if (sortNeed) {
                cell = row.createCell(i);
                cell.setCellValue(columnNames.get(i - 1));
            } else {
                cell = row.createCell(i - 1);
                cell.setCellValue(columnNames.get(i - 1));
            }
        }
        int index = 1;
        XSSFCellStyle cellStyle1 = wb.createCellStyle();
        cellStyle1.setAlignment(HorizontalAlignment.LEFT);
        for (int a = 0; a < dataList.size(); a++) {
            row = sheet.createRow(sheet.getLastRowNum() + 1);
            // 为数据内容设置特点新单元格样式1 自动换行 上下居中
            XSSFCell datacell;
            if (sortNeed) {
                datacell = row.createCell(0);
                datacell.setCellValue(String.valueOf(index));
            }
            for (int j = 1; j <= keys.size(); j++) {
                if (sortNeed) {
                    datacell = row.createCell(j);
                } else {
                    datacell = row.createCell(j - 1);
                }
                datacell.setCellStyle(cellStyle1);
                Object cellValue = null;
                try {
                    cellValue = ReflectionUtil.getFieldValue(dataList.get(a), keys.get(j - 1));
                } catch (Exception e) {
                }
                if (Objects.isNull(cellValue)) {
                    cellValue = StringUtils.EMPTY;
                }
                if (cellValue instanceof BigDecimal) {
                    datacell.setCellValue(((BigDecimal) cellValue).doubleValue());
                } else if (cellValue instanceof Double) {
                    datacell.setCellValue((Double) cellValue);
                } else if (cellValue instanceof Short) {
                    datacell.setCellValue(((Short) cellValue).doubleValue());
                } else if (cellValue instanceof Integer) {
                    datacell.setCellValue(((Integer) cellValue).doubleValue());
                } else if (cellValue instanceof Date) {
                    if (!StringUtils.EMPTY.equals(cellValue)) {
                        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        datacell.setCellValue(df.format(cellValue));
                    }
                } else {
                    datacell.setCellValue(String.valueOf(cellValue));
                }
            }
            index++;
        }
        wb.setSheetName(0, sheetName);
        return wb;
    }

//    public String saveFileAndPutOss(Workbook hssfWorkbook, String fileName, User user, String fileAddress) {
//        // 第六步，将文件存到指定位置
//        try {
//            // 上传文件。
//            ByteArrayOutputStream baos = new ByteArrayOutputStream();
//            hssfWorkbook.write(baos);
//            ByteArrayInputStream swapStream = new ByteArrayInputStream(baos.toByteArray());
//            return this.updateSheet(swapStream, fileName, user, fileAddress);
//        } catch (Exception e) {
//            e.printStackTrace();
//            return null;
//        }
//
//    }

    public void export(HttpServletResponse response, Workbook hssfWorkbook, String fileName) throws IOException {
        // 第六步，将文件存到浏览器设置的下载位置
        String filename = fileName + ".xls";
        response.setContentType("application/ms-excel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename="
                .concat(String.valueOf(URLEncoder.encode(filename, "UTF-8"))));
        OutputStream out = response.getOutputStream();
        try {
            hssfWorkbook.write(out);// 将数据写出去
            String str = "导出" + fileName + "成功！";
            System.out.println(str);
        } catch (Exception e) {
            e.printStackTrace();
            String str1 = "导出" + fileName + "失败！";
            System.out.println(str1);
        } finally {
            out.close();
        }
    }

    /**
     * 利用反射  根据属性名获取属性值
     */
    private static Object getFieldValueByName(String fieldName, Object o) {
        try {
            if (o instanceof Map) {
                return ((Map) o).get(fieldName);
            } else {
                String firstLetter = fieldName.substring(0, 1).toUpperCase();
                String getter = "get" + firstLetter + fieldName.substring(1);
                Method method = o.getClass().getMethod(getter, new Class[]{});
                Object value = method.invoke(o, new Object[]{});
                return value;
            }

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * List<Map<String, String>>
     * List<String[]>
     *
     * @param sheetName
     * @throws Exception
     */
    public static XSSFWorkbook fuzzy(String sheetName, List... datas) {

        // 第一步，创建一个webbook，对应一个Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);
        sheet.setDefaultColumnWidth(15); //统一设置列宽

        // 创建第0行 也就是标题
        XSSFRow row1 = sheet.createRow((int) 0);
        row1.setHeightInPoints(50);// 设备标题的高度
        // 第三步创建标题的单元格样式style2以及字体样式headerFont1
        XSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
//        style2.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_TURQUOISE.getIndex());
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        XSSFFont headerFont1 = wb.createFont(); // 创建字体样式
        headerFont1.setBold(Boolean.TRUE); // 字体加粗
        headerFont1.setFontName("黑体"); // 设置字体类型
        headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
        style2.setFont(headerFont1); // 为标题样式设置字体样式

        XSSFCell cell1 = row1.createCell(0);// 创建标题第一列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
                9)); // 合并列标题
        cell1.setCellValue(sheetName); // 设置值标题
        cell1.setCellStyle(style2); // 设置标题样式

        for (List data : datas) {
            boolean isMap = false;
            for (Object d : data) {
                if (d instanceof Map) {
                    isMap = true;
                } else {
                    isMap = false;
                }
                break;
            }
            if (isMap) {
                List<Map<String, String>> dd = (List<Map<String, String>>) data;
                for (Map<String, String> map : dd) {
                    XSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
                    int mapIndex = 0;
                    for (Map.Entry<String, String> entry : map.entrySet()) {

                        if (StringUtils.contains(entry.getKey(), "separator")) {
                            XSSFCell datacell = row.createCell(mapIndex);
                            datacell.setCellValue(StringUtils.EMPTY);
                            mapIndex += 1;
                            continue;
                        }
                        XSSFCell datacell = row.createCell(mapIndex);
                        datacell.setCellValue(entry.getKey());
                        XSSFCell datacell2 = row.createCell(mapIndex + 1);
                        datacell2.setCellValue(entry.getValue());
                        mapIndex += 2;
                    }
                }
            } else {
                List<Object[]> dd = (List<Object[]>) data;
                dd.forEach(object -> {
                    XSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
                    int listIndex = 0;
                    for (Object obj : object) {
                        XSSFCell datacell = row.createCell(listIndex);
                        if (obj instanceof BigDecimal) {
                            datacell.setCellValue(((BigDecimal) obj).doubleValue());
                        } else if (obj instanceof Double) {
                            datacell.setCellValue(((Double) obj).doubleValue());
                        } else if (obj instanceof Short) {
                            datacell.setCellValue(((Short) obj).doubleValue());
                        } else if (obj instanceof Integer) {
                            datacell.setCellValue(((Integer) obj).doubleValue());
                        } else {
                            datacell.setCellValue(String.valueOf(obj));
                        }
                        listIndex += 1;
                    }
                });
            }
        }
        return wb;
    }

    /**
     * 通过Object、方法名、参数去调用方法
     *
     * @param flag 是否是静态
     */
    private static Object invokeMethod(Boolean flag, Object entity,
                                       String methodName, Object... params) throws Exception {
        Object obj = entity;
        Class cls;
        if (flag) {
            cls = (Class) obj;
        } else {
            cls = obj.getClass();
        }
        Class[] classArgs = new Class[params.length];
        for (int i = 0; i < classArgs.length; i++) {
            classArgs[i] = params[i].getClass();
        }
        Method method;
        try {
            method = cls.getDeclaredMethod(methodName, classArgs);// 包含privare方法
        } catch (Exception e) {
            method = cls.getMethod(methodName, classArgs);
        }
        Object result = method.invoke(flag ? null : obj, params);
        return result;
    }

    /**
     * 导出多个sheet
     */
    public XSSFWorkbook executeSheet(XSSFWorkbook wb, String sheetName, String titleName, List<String> columnNames, List<String> mustList,
                                     List<String> keys, List dataList, Map<Integer, List<String>> optionsMap, Boolean sortNeed) {
        // 第一步，创建一个webbook，对应一个Excel文件
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);
        XSSFDataFormat format = wb.createDataFormat();

        //配置下拉菜单
        Set set = optionsMap.keySet();
        set.forEach(k -> {
            String[] datas = optionsMap.get(k).toArray(new String[optionsMap.get(k).size()]);
            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
            XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(datas);
            CellRangeAddressList addressList = new CellRangeAddressList(0, 1000, (int) k, (int) k);
            XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(
                    dvConstraint, addressList);
            sheet.addValidationData(validation);
        });

        sheet.setDefaultColumnWidth(20); //统一设置列宽
        int firstRowNum = 0;
        if (StringUtils.isNotEmpty(titleName)) {
            // 创建第0行 也就是标题
            XSSFRow row1 = sheet.createRow(firstRowNum);

            row1.setHeightInPoints(50);// 设备标题的高度
            // 第三步创建标题的单元格样式style2以及字体样式headerFont1
            XSSFCellStyle style2 = wb.createCellStyle();
            style2.setAlignment(HorizontalAlignment.CENTER);
            style2.setVerticalAlignment(VerticalAlignment.CENTER);

            style2.setFillForegroundColor((short) 0x29);
            //style2.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_TURQUOISE.getIndex());
            style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            XSSFFont headerFont1 = wb.createFont(); // 创建字体样式
            headerFont1.setBold(Boolean.TRUE); // 字体加粗
            headerFont1.setFontName("宋体"); // 设置字体类型
            headerFont1.setFontHeightInPoints((short) 10); // 设置字体大小
            style2.setFont(headerFont1); // 为标题样式设置字体样式
            style2.setDataFormat(format.getFormat("@"));
            XSSFCell cell1 = row1.createCell(0);// 创建标题第一列
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
                    columnNames.size())); // 合并列标题
            cell1.setCellValue(titleName); // 设置值标题
            cell1.setCellStyle(style2); // 设置标题样式
            firstRowNum++;
        }
        // 创建第1行 也就是表头
        XSSFRow row = sheet.createRow(firstRowNum);
        row.setHeightInPoints(15);// 设置表头高度

        // 第四步，创建表头单元格样式 以及表头的字体样式
        XSSFCell cell = null;
        if (sortNeed) {
            cell = row.createCell(0);
            cell.setCellValue("序号");
        }
        for (int i = 1; i <= columnNames.size(); i++) {
            if (sortNeed) {
                cell = row.createCell(i);
                cell.setCellValue(columnNames.get(i - 1));
            } else {
                cell = row.createCell(i - 1);
                cell.setCellValue(columnNames.get(i - 1));
                XSSFCellStyle style2 = wb.createCellStyle();
                XSSFFont headerFont1 = wb.createFont(); // 创建字体样式
                if (mustList.contains(columnNames.get(i - 1))) {
                    headerFont1.setColor(HSSFColor.RED.index);//必传字段为红色
                } else {
                    headerFont1.setColor(HSSFColor.DARK_BLUE.index);
                }
//                headerFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                headerFont1.setFontHeightInPoints((short) 10);
                headerFont1.setFontName("宋体");
                style2.setFont(headerFont1); // 为标题样式设置字体样式
                style2.setDataFormat(format.getFormat("@"));
                cell.setCellStyle(style2);
            }
        }
        int index = 1;
        XSSFCellStyle cellStyle1 = wb.createCellStyle();
        cellStyle1.setAlignment(HorizontalAlignment.LEFT);
        for (int a = 0; a < dataList.size(); a++) {
            int s = sheet.getLastRowNum();
            row = sheet.createRow(sheet.getLastRowNum() + 1);
            // 为数据内容设置特点新单元格样式1 自动换行 上下居中
            XSSFCellStyle autoCellStyle = wb.createCellStyle();
            XSSFCell datacell = null;
            if (sortNeed) {
                datacell = row.createCell(0);
                datacell.setCellValue(String.valueOf(index));
            }

            for (int j = 1; j <= keys.size(); j++) {
                if (sortNeed) {
                    datacell = row.createCell(j);
                } else {
                    datacell = row.createCell(j - 1);
                }
                datacell.setCellStyle(cellStyle1);
                Object cellValue = getFieldValueByName(keys.get(j - 1), dataList.get(a));
                if (Objects.isNull(cellValue)) {
                    cellValue = StringUtils.EMPTY;
                }
                if (cellValue instanceof BigDecimal) {
                    datacell.setCellValue(((BigDecimal) cellValue).doubleValue());
                } else if (cellValue instanceof Double) {
                    datacell.setCellValue(((Double) cellValue).doubleValue());
                } else if (cellValue instanceof Short) {
                    datacell.setCellValue(((Short) cellValue).doubleValue());
                } else if (cellValue instanceof Integer) {
                    datacell.setCellValue(((Integer) cellValue).doubleValue());
                } else if (cellValue instanceof Date) {
                    if (!StringUtils.EMPTY.equals(cellValue)) {
                        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        datacell.setCellValue(df.format(cellValue));
                    }
                } else {
                    datacell.setCellValue(String.valueOf(cellValue));
                }
            }
            index++;
        }
        return wb;
    }

//    public String updateSheet(ByteArrayInputStream file, String fileName, User loginUser, String fileAddress) {
//        String prefixKey = fileAddress;
//        // 上传文件。
//        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
//        String name = fileName + loginUser.getName() + sdf.format(new Date());//文件名称
//        prefixKey = prefixKey + name + ".xlsx";
//        // Endpoint以杭州为例，其它Region请按实际情况填写。
//        String endpoint = this.endpoint;
//        // 阿里云主账号AccessKey拥有所有API的访问权限，风险很高。强烈建议您创建并使用RAM账号进行API访问或日常运维，请登录 https://ram.console.aliyun.com 创建RAM账号。
//        String accessKeyId = this.accessKeyId;
//        String accessKeySecret = this.accessKeySecret;
//        // 创建OSSClient实例。
//        OSSClient ossClient = new OSSClient(endpoint, accessKeyId, accessKeySecret);
//        // 判断文件是否存在。doesObjectExist还有一个参数isOnlyInOSS，如果为true则忽略302重定向或镜像；如果为false，则考虑302重定向或镜像。
//        boolean found = ossClient.doesObjectExist(bucketName, prefixKey);
//        if (found) {
//            try {
//                Thread.sleep(10);
//            } catch (Exception e) {
//            }
//            String newfileName = fileName + loginUser.getName() + sdf.format(new Date());
//            prefixKey = fileAddress + newfileName + ".xlsx";
//            return getUrl(file, prefixKey, ossClient);
//        } else {
//            return getUrl(file, prefixKey, ossClient);
//        }
//    }

//    private String getUrl(ByteArrayInputStream file, String prefixKey, OSSClient ossClient) {
//        // 设置URL过期时间。
//        Date expiration = new Date(System.currentTimeMillis() + Long.valueOf(this.getTimeout()));
//        // 生成以GET方法访问的签名URL，访客可以直接通过浏览器访问相关内容。
//        URL url = ossClient.generatePresignedUrl(bucketName, prefixKey, expiration);
//        try {
//            ossClient.putObject(this.bucketName, prefixKey, file);
//        } finally {
//            // 关闭OSSClient。
//            ossClient.shutdown();
//        }
//        return url.toString();
//    }
}
